package com.yncmcc.Repository;

import com.yncmcc.Entity.*;


import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.yncmcc.Repository.Interface.ContractsRepositoryCustom;
import org.hibernate.Session;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.*;

/**
 * Created by CMCC-Sunmo on 2016/9/19.
 */
public class ContractsRepositoryImpl implements ContractsRepositoryCustom {
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<ContractsCustomEntity> findReqContract(ContractsEntity contractsEntity) {

        List<Short> dept = new ArrayList<Short>();

        //此处的userId为userName
        List<ContractsCustomEntity> result = entityManager.createNativeQuery(
                "SELECT c.*,u.userName,d.deptName " +
                        "FROM contracts c, user u, department d " +
                        "WHERE c.userID = u.userID and c.deptID = d.deptID " +
                        "and c.contractStatus = 1 " +
                        "and c.deptID like :deptID " +
                        "and u.userName like :userName " +
                        "and c.contractSerial like :contractSerial " +
                        "and c.contractName like :contractName " +
                        "and c.contractCate like :contractCate " +
                        "order by c.contractCate, u.userRight, c.deptID, u.userName", ContractsCustomEntity.class)
                .setParameter("deptID", contractsEntity.getDeptId()==null || contractsEntity.getDeptId().equals("0")?"%%":"%"+contractsEntity.getDeptId()+"%")
                .setParameter("userName", contractsEntity.getUserId()==null?"%%":"%"+contractsEntity.getUserId()+"%")
                .setParameter("contractSerial", contractsEntity.getContractSerial()==null?"%%":"%"+contractsEntity.getContractSerial()+"%")
                .setParameter("contractName", contractsEntity.getContractName()==null?"%%":"%"+contractsEntity.getContractName()+"%")
                .setParameter("contractCate", contractsEntity.getContractCate()==null || contractsEntity.getContractCate()==0 ?"%%":"%"+contractsEntity.getContractCate()+"%")
                .getResultList();

        return result;
    }


    //邮件提醒
    //在用户设置的合同提醒时间到结束时间一直发邮件提醒，直到用户取消提醒
    @Override
    public List<ContractsCustomEntity> findBySrRemindTime() {

        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String srRemindTime = sdf.format(new Date());
        Timestamp srRemindTimeEnd = Timestamp.valueOf(srRemindTime +" 23:59:59");
        Timestamp srEndTime = Timestamp.valueOf(srRemindTime + " 00:00:00");

        List<ContractsCustomEntity> result = entityManager.createNativeQuery(
                "SELECT c.*,u.userName,d.deptName " +
                        "FROM contracts c, user u, department d " +
                        "WHERE c.userID = u.userID and c.deptID = d.deptID " +
                        "and c.contractStatus = 1 " +
                        "and c.srRemindTime BETWEEN c.srRemindTime and :srRemindTimeEnd " +
                        "and c.srIsWarn = 1 " +
                        "and c.srEndTime BETWEEN :srEndTime and c.srEndTime " +
                        "and c.contractCate = 1",ContractsCustomEntity.class)
                .setParameter("srRemindTimeEnd", srRemindTimeEnd)
                .setParameter("srEndTime",srEndTime)
                .getResultList();
        return result;
    }

    @Override
    public List<ContractsCustomEntity> findByEaRemindTime() {

        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String eaRemindTime = sdf.format(new Date());
        Timestamp eaRemindTimeEnd = Timestamp.valueOf(eaRemindTime +" 23:59:59");
        Timestamp eaEndTime = Timestamp.valueOf(eaRemindTime + " 00:00:00");

        List<ContractsCustomEntity> result = entityManager.createNativeQuery(
                "SELECT c.*,u.userName,d.deptName " +
                        "FROM contracts c, user u, department d " +
                        "WHERE c.userID = u.userID and c.deptID = d.deptID " +
                        "and c.contractStatus = 1 " +
                        "and c.eaRemindTime BETWEEN c.eaRemindTime and :eaRemindTimeEnd " +
                        "and c.eaEndTime BETWEEN :eaEndTime and c.eaEndTime " +
                        "and c.eaIsWarn = 1 " +
                        "and c.contractCate = 2",ContractsCustomEntity.class)
                .setParameter("eaRemindTimeEnd", eaRemindTimeEnd)
                .setParameter("eaEndTime",eaEndTime)
                .getResultList();
        return result;
    }

    @Override
    public List<ContractsCustomEntity> findByCtRemindTime() {

        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String ctRemindTime = sdf.format(new Date());
        Timestamp ctRemindTimeEnd = Timestamp.valueOf(ctRemindTime +" 23:59:59");
        Timestamp ctEndTime = Timestamp.valueOf(ctRemindTime + " 00:00:00");

        List<ContractsCustomEntity> result = entityManager.createNativeQuery(
                "SELECT c.*,u.userName,d.deptName " +
                        "FROM contracts c, user u, department d " +
                        "WHERE c.userID = u.userID and c.deptID = d.deptID " +
                        "and c.contractStatus = 1 " +
                        "and c.ctRemindTime BETWEEN c.ctRemindTime and :ctRemindTimeEnd " +
                        "and c.ctEndTime BETWEEN :ctEndTime and c.ctEndTime " +
                        "and c.ctIsWarn = 1 " +
                        "and c.contractCate = 3",ContractsCustomEntity.class)
                .setParameter("ctRemindTimeEnd", ctRemindTimeEnd)
                .setParameter("ctEndTime",ctEndTime)
                .getResultList();
        return result;
    }
}
